﻿Imports Microsoft.Office.Interop
Imports System.IO

Public Class Form1
    Dim Archivo As String = Application.StartupPath & "\Default.xls"
    Dim excelApp As New Excel.Application()
    Dim excelBook As Excel.Workbook
    Dim excelWorksheet As Excel.Worksheet
    Dim exec As DotNetDal.SQLPersistence
 

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        cmb_anio.DisplayMember = "anio"
        cmb_anio.ValueMember = "anio"
        cmb_anio.DataSource = DotNetDal.Generica.GetAnios(2011, Now.Year)

        cmb_mes.DisplayMember = "nombre"
        cmb_mes.ValueMember = "codigo"
        cmb_mes.DataSource = DotNetDal.Generica.GetMeses()

    End Sub
    Private Sub btn_procesar_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_procesar.Click
        Dim Archivo_Copia As String = Application.StartupPath & "\InformeGestion_" & Format(Now, "yyyyMMdd HHmmss") & ".xls"
        File.Copy(Archivo, Archivo_Copia)      
        excelApp.Workbooks.Open(Archivo_Copia)
        excelBook = excelApp.Workbooks(1)

        exec = New DotNetDal.SQLPersistence(False, "cnn", DotNetDal.SQLPersistence.Provider.SQLCLIENT)

        Dim reportes As Integer = Configuration.ConfigurationManager.AppSettings("nro_reportes")    'System.Configuration.configurationmanage.AppSettings("nro_reportes")
        Dim ii As Integer
        Dim jj As Integer = reportes

        For ii = 1 To reportes
            Dim sp As String = Configuration.ConfigurationManager.AppSettings(jj & "_TB")
            Dim FI As Integer = Configuration.ConfigurationManager.AppSettings(jj & "_fi")
            Dim CI As Integer = Configuration.ConfigurationManager.AppSettings(jj & "_ci")
            Dim Hoja As String = Configuration.ConfigurationManager.AppSettings(jj & "_hoja")
            excelWorksheet = excelBook.Worksheets(Hoja)
            excelWorksheet.Cells(1, 13) = Me.cmb_mes.Text
            excelWorksheet.Cells(1, 14) = Me.cmb_anio.Text

            Dim rp As New DotNetResponse.SQLPersistence           
            exec.ExecuteDataTable(rp, " select * from T" & IIf(Len(jj.ToString) = 1, "0" & jj, jj))
            If rp.TieneDatos Then
                Dim k As Integer = FI

                Dim l As Integer
                Dim i As Integer

                For i = 3 To rp.DtTable.Rows.Count - 1
                    excelWorksheet.Rows(FI + 1 & ":" & FI + 1).Select()
                    excelWorksheet.Cells(FI + 1, 1).select()
                    excelWorksheet.Range(excelWorksheet.Cells(FI + 1, 1), excelWorksheet.Cells(FI + 1, 1).End(Excel.XlDirection.xlToRight)).Insert( _
                            Shift:=Excel.XlInsertShiftDirection.xlShiftDown, CopyOrigin:=Excel.XlInsertFormatOrigin.xlFormatFromLeftOrAbove)
                Next
              
                For i = 0 To rp.DtTable.Rows.Count - 1
                    Dim j As Integer = CI
                    For l = 0 To rp.DtTable.Columns.Count - 1
                        Application.DoEvents()
                        excelWorksheet.Cells(k, j) = rp.DtTable.Rows(i).Item(l)
                        j = j + 1
                    Next
                    k = k + 1
                Next
            End If

            jj = jj - 1
        Next
        excelBook.Save()
        excelApp.Visible = True


    End Sub
End Class
